package no.ntnu.fp.storage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DatabaseEstablishment {

	static Connection conn;
	
	private String userName = "esbena_fp23";
	private String password = "thomas";
	private String serverName = "mysql.stud.ntnu.no";
	private String dbms = "mysql";
	private String portNumber; //DONNO
	private String dbName = "esbena_felles";
	private Statement st;
	private ResultSet rs;
	
	/*
	 * Changed names to english for consistency purposes
	 */
	
	private String[] sqlList2 = 
	   {"CREATE TABLE Person(Navn VARCHAR(50) NOT NULL, Epost VARCHAR(100), Brukernavn VARCHAR(50), Passord VARCHAR(30) NOT NULL, PRIMARY KEY(Brukernavn))",
		"CREATE TABLE Avtale(AvtaleID INT(32) UNSIGNED  AUTO_INCREMENT, Tittel VARCHAR(60) NOT NULL, Type String NOT NULL, startDato DATETIME, sluttDato DATETIME,  startTid Time, slutTid Time, Beskrivelse MediumText, EksterneDeltagere int, AliveFlag bool, sted text, PRIMARY KEY(AvtaleID));",
		"CREATE TABLE Rom(RomNr VARCHAR(20), kapasitet int, PRIMARY KEY(RomNr));",
		"CREATE TABLE Melding(MeldingID int AUTO_INCREMENT, Person VARCHAR(50) NOT NULL, AvtaleID INT(32) UNSIGNED , Type int NOT NULL, PRIMARY KEY(MeldingID), FOREIGN KEY(AvtaleID) references Avtale(AvtaleID), FOREIGN KEY(person) references Person(Brukernavn));",
		"CREATE TABLE AvtaleTilRom(AvtaleID INT(32) UNSIGNED , RomNr VARCHAR(20), PRIMARY KEY(AvtaleID, RomNr), FOREIGN KEY(AvtaleID) references Avtale(AvtaleID), FOREIGN KEY(RomNr) references Rom(RomNr));",
		"CREATE TABLE AvtaleTilDeltaker(Brukernavn VARCHAR(50), AvtaleID INT(32) UNSIGNED , Admin bool, Deltar int(2), PRIMARY KEY (Brukernavn, AvtaleID), FOREIGN KEY(Brukernavn) references Person(Brukernavn), FOREIGN KEY(AvtaleID) references Avale(AvtaleID));"
	};
	/*
	 * Translated to english for more concistency
	 */
	private String[] sqlList3 = 
	   {"CREATE TABLE Person(PersonId INT NOT NULL, Name VARCHAR(50) NOT NULL, Email VARCHAR(100), Username VARCHAR(50), Password VARCHAR(30) NOT NULL, PRIMARY KEY(PersonId))",
		"CREATE TABLE Appointment(AppointmentId INT(32) UNSIGNED AUTO_INCREMENT, Title VARCHAR(60) NOT NULL, AppointmentType bool, StartDate DATETIME, EndDate DATETIME, StartTime Time, EndTime Time, Description MediumText, ExternalParticipants INT, AliveFlag bool, Place text, RoomId INT, PRIMARY KEY(AppointmentId), FOREIGN KEY(RoomId) REFERENCES Room(RoomId));",
		"CREATE TABLE Room(RoomId INT, Name VARCHAR(20), Capacity int, PRIMARY KEY(RoomId));",
		"CREATE TABLE Message(MessageId INT AUTO_INCREMENT, PersonId INT NOT NULL, AppointmentId INT(32) UNSIGNED, MessageType INT, Active bool PRIMARY KEY(MessageId), FOREIGN KEY(AppointmentId) references Appointment(AppointmentId), FOREIGN KEY(PersonId) references Person(PersonId));",
		"CREATE TABLE AppointmentToUser(PersonId INT NOT NULL, AppointmentId INT(32) UNSIGNED , Administrator bool, Participate int(2), PRIMARY KEY (PersonId, AppointmentId), FOREIGN KEY(PersonId) references Person(PersonId), FOREIGN KEY(AppointmentId) references Appointment(AppointmentId));"
	};
	
	
	public void initializeConnection() throws SQLException {
		Connection conn = null;
		Properties connectionProps = new Properties();
		connectionProps.put("user", this.userName);
		connectionProps.put("password", this.password);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		if (this.dbms.equals("mysql")) {
			conn = DriverManager.
		    getConnection("jdbc:" + this.dbms + "://" + this.serverName +
		                      "/" + this.dbName , connectionProps);
			
		} else if (this.dbms.equals("derby")) {
		    conn = DriverManager.
		    getConnection("jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps);
		}
		 System.out.println("Connected to database");
		 st = conn.createStatement();
		 generateDatabase();
	}
	
	private void generateDatabase(){
		for (String sqlString : sqlList3) {
			try {
				st.executeUpdate(sqlString);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				Thread.sleep(500);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
	}
	
	public static void main(String[] args){
		DatabaseEstablishment de = new DatabaseEstablishment();
		try {
			de.initializeConnection();
		} catch (SQLException e) {
			
			System.err.println("The fuck happened here?");
			e.printStackTrace();
		}
		
	}
}
